﻿-- =============================================
-- Script Template
-- =============================================
use webmodel
go


/***********************添加用户***********************/

if exists (select * from sysobjects where name='user_add')
drop procedure user_add
go
create procedure user_add
@UserName varchar(100),
@UserPwd varchar(100),
@RealName varchar(100),
@HeadImg varchar(100),
@CityID int,
@Role int,
@EndDate datetime,
@IsPush bit,
@CreateDate datetime
AS 
	INSERT INTO [user](
	[UserName],[UserPwd],[RealName],[HeadImg],[CityID],[Role],[EndDate],[IsPush],[CreateDate]
	)VALUES(
	@UserName,@UserPwd,@RealName,@HeadImg,@CityID,@Role,@EndDate,@IsPush,@CreateDate
	)
	return @@IDENTITY
go


/***********************验证用户***********************/
if exists (select * from sysobjects where name='user_login')
drop procedure user_login
go
create procedure user_login
@UserName varchar(50),
@UserPwd varchar(50)
AS 
	select [user].* from [user] where UserName=@UserName and UserPwd=@UserPwd order by [user].ID desc
go


--获取用户
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'user_page')
	BEGIN
		DROP  Procedure  user_page
	END
GO
CREATE PROCEDURE user_page
    @startindex INT, --请求的页码
    @pagesize INT ,--每页显示的记录数
	@order varchar(50),
	@desc varchar(50),
	@filter nvarchar(max),--查询条件
	@count int output
AS
declare @sql nvarchar(max)
set @sql='with orderlist as(select row_number() over( order by [user].'+@order+' '+@desc +') as rownumber,[user].*,[depart].Name as DepartName from [user] left join [depart] on [user].DepartID=[depart].ID where 1=1 '+@filter+') select * from orderlist where rownumber between '+convert(varchar(20),(@startindex-1)*@pagesize+1)+' and '+convert(varchar(20),(@startindex-1)*@pagesize+@pagesize)
set @sql=@sql + ';' + ' set @p=(select count(1) from [user] left join [depart] on [user].DepartID=[depart].ID  where 1=1 '+ @filter +')'
exec sp_executesql 
     @stmt=@sql,
     @params=N'@p as int output',
     @p=@count output
go






--获取公告
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'notice_page')
	BEGIN
		DROP  Procedure  notice_page
	END
GO
CREATE PROCEDURE notice_page
    @startindex INT, --请求的页码
    @pagesize INT ,--每页显示的记录数
	@order varchar(50),
	@desc varchar(50),
	@filter nvarchar(max),--查询条件
	@count int output
AS
declare @sql nvarchar(max)
set @sql='with orderlist as(select row_number() over( order by [notice].'+@order+' '+@desc +') as rownumber,[notice].*,[user].RealName as RealName,[user].HeadImg as HeadImg from [notice] left join [user] on [notice].UserID=[user].ID where 1=1 '+@filter+') select * from orderlist where rownumber between '+convert(varchar(20),(@startindex-1)*@pagesize+1)+' and '+convert(varchar(20),(@startindex-1)*@pagesize+@pagesize)
set @sql=@sql + ';' + ' set @p=(select count(1) from [notice] left join [user] on [notice].UserID=[user].ID where 1=1 '+ @filter +')'
exec sp_executesql 
     @stmt=@sql,
     @params=N'@p as int output',
     @p=@count output
go





--根据某列获取某列数据
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'findcolumn_by_column')
	BEGIN
		DROP  Procedure findcolumn_by_column
	END
GO
CREATE PROCEDURE findcolumn_by_column
@column varchar(40),
@wcolumn varchar(40),
@wcolumnvalue varchar(max),
@table varchar(40)
As
   declare @sql nvarchar(max)
   set @sql='select '+@column+' from '+@table+' where '+ @wcolumn + ' = ''' + @wcolumnvalue +'''';
   exec sp_executesql @sql;
go


--通过某1列查找数据
if exists (select * from sysobjects where name='findlist_by_columns')
drop procedure findlist_by_columns
go
create procedure findlist_by_columns
@column varchar(50),
@columnvalue varchar(max),
@table varchar(50)
as
  declare @sql nvarchar(max)
  set @sql='select * from ['+@table+'] where '+@column+' in ('+@columnvalue+')';
  exec sp_executesql @sql
go


/********************分页存储过程**********************/
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'table_page')
	BEGIN
		DROP  Procedure  table_page
	END
GO
CREATE PROCEDURE table_page
    @startindex INT, --请求的页码
    @pagesize INT ,--每页显示的记录数
	@order varchar(50),
	@desc varchar(50),
	@filter nvarchar(max),--查询条件
	@table varchar(100),
	@count int output
AS
declare @sql nvarchar(max)
set @sql='with orderlist as(select row_number() over( order by '+@order+' '+@desc +') as rownumber, * from '+@table+' where 1=1 '+@filter+') select * from orderlist where rownumber between '+convert(varchar(20),(@startindex-1)*@pagesize+1)+' and '+convert(varchar(20),(@startindex-1)*@pagesize+@pagesize)
set @sql=@sql + ';' + ' set @p=(select count(1) from ' + @table + ' where 1=1 '+ @filter +')'
exec sp_executesql 
     @stmt=@sql,
     @params=N'@p as int output',
     @p=@count output
go




/********************获取前n条存储过程**********************/
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'table_top')
	BEGIN
		DROP  Procedure  table_top
	END
GO
CREATE PROCEDURE table_top
    @top INT ,--前几条
	@order varchar(50),
	@desc varchar(50),
	@filter nvarchar(max),--查询条件
	@table varchar(100)
AS
declare @sql nvarchar(max)
set @sql='select top '+convert(varchar(20), @top)+' * from '+@table+' where 1=1 '+@filter + ' order by '+@order+' '+@desc
exec sp_executesql @sql
go


/********************获取表数量存储过程**********************/
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'find_count')
	BEGIN
		DROP  Procedure  find_count
	END
GO
CREATE PROCEDURE find_count
	@filter nvarchar(max),--查询条件
	@table varchar(100)
AS
declare @sql nvarchar(max)
set @sql='select count(ID) from '+@table+' where 1=1 and '+@filter
exec sp_executesql @sql
go


--删除任意表中id值的数据
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'delete_by_id')
	BEGIN
		DROP  Procedure delete_by_id
	END
GO
CREATE PROCEDURE delete_by_id
    @id int,
    @table varchar(50)
As
	declare @sql nvarchar(max)
    set @sql='delete from '+@table+' where id ='+convert(varchar(10),@id);
    exec sp_executesql @sql
go


--删除任意表中id值的数据
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'delete_by_ids')
	BEGIN
		DROP  Procedure delete_by_ids
	END
GO
CREATE PROCEDURE delete_by_ids
    @ids varchar(max),
    @table varchar(50)
As
	declare @sql nvarchar(max)
    set @sql='delete from '+@table+' where id in ('+@ids+')';
    exec sp_executesql @sql
go

--通过ID查找表

if exists (select * from sysobjects where name='findone_by_id')
drop procedure findone_by_id
go
create procedure findone_by_id
@id int,
@table varchar(50)
as
  declare @sql nvarchar(max)
  set @sql='select * from '+@table+' where id='+convert(varchar(10),@id)
  exec sp_executesql @sql
go


--根据id获取某列数据
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'findcolumn_by_id')
	BEGIN
		DROP  Procedure findcolumn_by_id
	END
GO
CREATE PROCEDURE findcolumn_by_id
@id int,
@column varchar(40),
@table varchar(40)
As
   declare @sql nvarchar(max)
   set @sql='select '+@column+' from '+@table+' where id = '+convert(varchar(10),@id)
   exec sp_executesql @sql
go


--通过IDs查找表
if exists (select * from sysobjects where name='findlist_by_ids')
drop procedure findlist_by_ids
go
create procedure findlist_by_ids
@ids varchar(max),
@table varchar(50)
as
  declare @sql nvarchar(max)
  set @sql='select * from '+@table+' where id in ('+@ids+')'
  exec sp_executesql @sql
go

--通过comid和某1列查找数据
if exists (select * from sysobjects where name='findlist_by_column')
drop procedure findlist_by_column
go
create procedure findlist_by_column
@column varchar(50),
@columnvalue varchar(max),
@table varchar(50)
as
  declare @sql nvarchar(max)
  set @sql='select * from ['+@table+'] where IsDelete=0 and '+@column+'='''+@columnvalue+'''';
  exec sp_executesql @sql
go


--修改任意表中特定列值的数据
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'updatecolumn_by_id')
	BEGIN
		DROP  Procedure updatecolumn_by_id
	END
GO
CREATE PROCEDURE updatecolumn_by_id
@id int,
@column varchar(40),
@value varchar(max),
@table varchar(40)
As
   declare @sql nvarchar(max)
   set @sql='update '+@table+' set '+ @column+'='''+@value+''' where id ='+convert(varchar(10),@id);
   exec sp_executesql @sql;
go


--根据某列修改任意表中特定列值的数据 （单改）
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'updatecolumn_by_column')
	BEGIN
		DROP  Procedure updatecolumn_by_column
	END
GO
CREATE PROCEDURE updatecolumn_by_column
@column varchar(40),
@value varchar(max),
@wcolumn varchar(40),
@wvalue varchar(max),
@table varchar(40)
As
   declare @sql nvarchar(max)
   set @sql='update '+@table+' set '+ @column+'='''+@value+''' where '+@wcolumn+' = '''+@wvalue+'''';
   exec sp_executesql @sql;
go


--获取单号通用方法
if exists (select * from sysobjects where name='findnum_by_date')
drop procedure findnum_by_date
go
create procedure findnum_by_date
@begindate datetime,
@enddate datetime,
@table varchar(50)
as
  declare @sql nvarchar(500)
  set @sql='select top 1 Num from '+@table+' where CreateDate between '''+convert(varchar(20), @begindate)+''' and '''+convert(varchar(20),@enddate)+''' order by id desc';
  exec sp_executesql @sql
go


/********************获取userids**********************/
if exists (select * from sysobjects where name='find_userids')
drop function find_userids
go
CREATE FUNCTION find_userids(
@sid  int  --外键id
)RETURNS @re TABLE(col varchar(8000))
AS
BEGIN
 declare @return varchar(8000)
 select @return=stuff((select ',' + convert(varchar(20),[userid]) from [reply] where sid=@sid and [state]=2 and flag='order' for xml path('')), 1, 1, '')
 if @return is NULL
 begin
	set @return=''
 end
 INSERT @re VALUES(@return)
 RETURN
END
GO